
package com.craterhill.java.ems.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.craterhill.java.ems.common.beans.Role;
import com.craterhill.java.ems.common.beans.Staff;
import com.craterhill.java.ems.dao.StaffDao;

@Repository
public class StaffDaoImpl extends AbstractDao<Staff> implements StaffDao {

	@Autowired
	public StaffDaoImpl(final DataSource dataSource) {
		super(dataSource);
	}

	@Override
	public int save(final Staff staff) {
		return super.save(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement preparedStatement = connection.prepareStatement(SAVE_STAFF);
				preparedStatement.setString(1, staff.getStaffNumber());
				preparedStatement.setInt(2, staff.getPositionId());
				preparedStatement.setString(3, staff.getUserName());
				preparedStatement.setString(4, staff.getPassword());
				preparedStatement.setString(5, staff.getFirstName());
				preparedStatement.setString(6, staff.getMiddleName());
				preparedStatement.setString(7, staff.getLastName());
				preparedStatement.setDate(8, staff.getDateOfBirth());
				preparedStatement.setBoolean(9, staff.isGender());
				preparedStatement.setString(10, staff.getStreet());
				preparedStatement.setString(11, staff.getArea());
				preparedStatement.setString(12, staff.getCity());
				preparedStatement.setInt(13, staff.getStateId());
				preparedStatement.setInt(14, staff.getCountryId());
				preparedStatement.setInt(15, staff.getZipCode());
				preparedStatement.setString(16, staff.getMobileNumber());
				preparedStatement.setString(17, staff.getHomePhoneNumber());
				preparedStatement.setString(18, staff.getOtherPhoneNumber());
				preparedStatement.setString(19, staff.getEmailId());
				preparedStatement.setString(20, staff.getImagePath());
				preparedStatement.setString(21, staff.getSpouseName());
				preparedStatement.setString(22, staff.getMoreInfo());
				preparedStatement.setString(23, staff.getQualification());
				preparedStatement.setString(24, staff.getUniversity());
				preparedStatement.setString(25, staff.getExperience());
				preparedStatement.setString(26, staff.getSubjectMatterExpert());
				preparedStatement.setString(27, staff.getAdditionalCurricularActivities());
				preparedStatement.setString(28, staff.getRewardsOrAchievements());
				preparedStatement.setString(29, staff.getMemberships());
				preparedStatement.setString(30, staff.getOthers());
				preparedStatement.setDate(31, staff.getDateOfHire());
				preparedStatement.setDate(32, staff.getDateOfTermination());
				preparedStatement.setDate(33, staff.getDateOfRetirement());
				preparedStatement.setBoolean(34, staff.isCanChangePassword());
				preparedStatement.setInt(35, staff.getCreatedBy());
				return preparedStatement;
			}
		});
	}

	@Override
	public boolean update(final Staff staff) {
		return super.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_STAFF);
				preparedStatement.setString(1, staff.getStaffNumber());
				preparedStatement.setInt(2, staff.getPositionId());
				preparedStatement.setString(3, staff.getUserName());
				preparedStatement.setString(4, staff.getPassword());
				preparedStatement.setString(5, staff.getFirstName());
				preparedStatement.setString(6, staff.getMiddleName());
				preparedStatement.setString(7, staff.getLastName());
				preparedStatement.setDate(8, staff.getDateOfBirth());
				preparedStatement.setBoolean(9, staff.isGender());
				preparedStatement.setString(10, staff.getStreet());
				preparedStatement.setString(11, staff.getArea());
				preparedStatement.setString(12, staff.getCity());
				preparedStatement.setInt(13, staff.getStateId());
				preparedStatement.setInt(14, staff.getCountryId());
				preparedStatement.setInt(15, staff.getZipCode());
				preparedStatement.setString(16, staff.getMobileNumber());
				preparedStatement.setString(17, staff.getHomePhoneNumber());
				preparedStatement.setString(18, staff.getOtherPhoneNumber());
				preparedStatement.setString(19, staff.getEmailId());
				preparedStatement.setString(20, staff.getImagePath());
				preparedStatement.setString(21, staff.getSpouseName());
				preparedStatement.setString(22, staff.getMoreInfo());
				preparedStatement.setString(23, staff.getQualification());
				preparedStatement.setString(24, staff.getUniversity());
				preparedStatement.setString(25, staff.getExperience());
				preparedStatement.setString(26, staff.getSubjectMatterExpert());
				preparedStatement.setString(27, staff.getAdditionalCurricularActivities());
				preparedStatement.setString(28, staff.getRewardsOrAchievements());
				preparedStatement.setString(29, staff.getMemberships());
				preparedStatement.setString(30, staff.getOthers());
				preparedStatement.setDate(31, staff.getDateOfHire());
				preparedStatement.setDate(32, staff.getDateOfTermination());
				preparedStatement.setDate(33, staff.getDateOfRetirement());
				preparedStatement.setBoolean(34, staff.isCanChangePassword());
				preparedStatement.setBoolean(35, staff.isStatus());
				preparedStatement.setDate(36, staff.getModifiedDate());
				preparedStatement.setInt(37, staff.getStaffId());
				return preparedStatement;
			}
		});
	}

	@Override
	public boolean remove(final Integer... staffIds) {
		return super.batchOperation(DELETE_STAFF, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement preparedStatement, int position) throws SQLException {
				preparedStatement.setInt(1, staffIds[position]);
			}

			@Override
			public int getBatchSize() {
				return staffIds.length;
			}
		});
	}

	@Override
	public Staff find(int recordId) {
		return super.findSingleObject(FIND_STAFF_BY_ID, new StaffExtractor(), recordId);
	}

	public Staff findByUsernameAndPassword(final String username, final String password) {
		return super.findSingleObject(FIND_STAFF_BY_USERNAME_PASSWORD, new StaffExtractor(), username, password);
	}

	public List<Role> findStaffAuthorities(final int staffId) {
		return getJdbcTemplate().query(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement preparedStatement = connection.prepareStatement(FIND_STAFF_AUTHORITIES);
				preparedStatement.setInt(1, staffId);
				return preparedStatement;
			}
		}, new RowMapper<Role>() {
			@Override
			public Role mapRow(ResultSet resultSet, int rowNum) throws SQLException {
				Role role = new Role();
				role.setRoleId(resultSet.getInt(1));
				role.setRoleName(resultSet.getString(2));
				role.setRoleShortName(resultSet.getString(3));
				return role;
			}
		});
	}

	@Override
	public List<Staff> findByRole(int roleId) {
		return super.findMultipleObject(FIND_STAFF_BY_ROLE_ID, new StaffExtractor(), roleId);
	}

	@Override
	public List<Staff> findAll() {
		return super.findMultipleObject(FIND_ALL_STAFF, new StaffExtractor());
	}
}
